SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




CREATE PROCEDURE [dbo].[usp_EmployerSearchRequire]
      (
        @JOL_Require_Company VARCHAR(50) ,
        @JOL_Require_Job VARCHAR(50)
      )
AS 
      SELECT    b.JOL_Employer_Name ,
                a.JOL_Require_ID ,
                a.JOL_Require_Company ,
                a.JOL_Require_Job ,
                a.JOL_Require_VerifyStatus ,
                a.JOL_Require_ClickNumber ,
                a.JOL_Require_AddTime ,
                CASE a.JOL_Require_Student_Type
                  WHEN 1 THEN '应届生'
                  ELSE '实习生'
                END ,
                a.JOL_Require_Study_Level ,
                a.JOL_Require_Profession ,
                a.OnlyForSysu
      FROM      dbo.JOL_Require AS a
      INNER JOIN dbo.JOL_Employer AS b
      ON        a.JOL_Require_Company = b.JOL_Employer_Account
      WHERE     ( b.JOL_Employer_Name = @JOL_Require_Company )
                AND ( a.JOL_Require_Job LIKE '%' + @JOL_Require_Job + '%' )
      ORDER BY  a.JOL_Require_VerifyStatus ,
                a.JOL_Require_AddTime DESC ,
                a.JOL_Require_UpdateTime DESC





GO
